RDS for PostgreSQLで14.2がサポートされたので、mysql_fdwを使ってMySQLへ外部接続してみた

いわさです。

RDS for PostgreSQLで新たなマイナーバージョン14.2, 13.6などが利用出来るようになりました。

RDS for PostgreSQL 14.2, 13.6では、mysql_fdw拡張機能がサポートされており、MySQ/MariaDBへの外部接続が出来ます。

EnterpriseDB/mysql_fdw: PostgreSQL foreign data wrapper for MySQL

ためしてみる

以下のようにRDSでPostgreSQL 14.2とMySQLを作成しておき、PostgreSQLからMySQLへアクセスしてみたいと思います。
間のセキュリティグループ設定などは済んでいます。

MySQL側でテーブルなどを作成

MySQLでDBとテーブルを作成しておきます。

mysql> create database hoge;
mysql> use hoge;
mysql> create table piyo (id int, name varchar(10), updatetime datetime DEFAULT NULL);
mysql> insert into piyo values (1, 'aaa1', '2021-10-27 9:00:00');
mysql> insert into piyo values (2, 'bbb1', '2021-10-27 9:00:00');
mysql> insert into piyo values (3, 'ccc1', '2021-10-27 9:00:00');

mysql> select * from piyo;
+------+------+---------------------+
| id   | name | updatetime          |
+------+------+---------------------+
|    1 | aaa1 | 2021-10-27 09:00:00 |
|    2 | bbb1 | 2021-10-27 09:00:00 |
|    3 | ccc1 | 2021-10-27 09:00:00 |
+------+------+---------------------+
3 rows in set (0.03 sec)

上記テーブルはユニークキーが設定されていません。
この状態だと、PostgreSQLからUPDATE, DELETE, INSERT操作をすることが出来ずSELECTしか出来ません。

postgres=> insert into mysqltbl values (4, 'ddd1', now());
ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation

ユニークキーを追加設定しておきます。

mysql> show columns from piyo;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int         | YES  |     | NULL    |       |
| name       | varchar(10) | YES  |     | NULL    |       |
| updatetime | datetime    | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

mysql> alter table piyo add unique (id);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from piyo;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int         | YES  | UNI | NULL    |       |
| name       | varchar(10) | YES  |     | NULL    |       |
| updatetime | datetime    | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

PostgreSQLで外部連携設定

PostgreSQL側では拡張機能の有効化、外部データの接続構成を設定、テーブルのマッピング、ロールへのアクセス許可を与えます。
ロールはpostgresqlなど特権ユーザーは利用できないようなので事前にテスト用のロールを作成しておきました。

postgres=> CREATE ROLE user1 WITH PASSWORD 'XXXXXXXX' LOGIN;

postgres=> CREATE EXTENSION mysql_fdw;
CREATE EXTENSION

postgres=> CREATE SERVER hogemysql FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'database-2.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com', port '3306');
CREATE SERVER

postgres=> GRANT USAGE ON FOREIGN SERVER hogemysql to user1;
GRANT

postgres=> CREATE USER MAPPING FOR user1 SERVER hogemysql OPTIONS (username 'admin', password 'XXXXXXXX');
CREATE USER MAPPING

postgres=> CREATE FOREIGN TABLE mysqltbl (id int, name text, updatetime timestamp) SERVER hogemysql OPTIONS (dbname 'hoge', table_name 'piyo');
CREATE FOREIGN TABLE

postgres=> GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mysqltbl TO user1;
GRANT

PostgreSQLでデータ操作

postgres=> insert into mysqltbl values (4, 'ddd1', now());
INSERT 0 1
postgres=> select * from mysqltbl;

id | name | updatetime
+------+---------------------
1 | aaa1 | 2021-10-27 09:00:00
2 | bbb1 | 2021-10-27 09:00:00
3 | ccc1 | 2021-10-27 09:00:00
4 | ddd1 | 2022-03-15 21:37:48
(4 rows)

PostgreSQL側のテーブルとMySQL側のテーブルを結合してみました。

postgres=> select a.id, a.name, b.name2, a.updatetime from mysqltbl a inner join fuga b on a.id = b.id;
 id | name | name2 |     updatetime      
----+------+-------+---------------------
  1 | aaa1 | aaa2  | 2021-10-27 09:00:00
  2 | bbb1 | bbb2  | 2021-10-27 09:00:00
  3 | ccc1 | ccc2  | 2021-10-27 09:00:00
(3 rows)

いけるな。

ちなみに、連携設定時のCREATE FOREIGN TABLEマッピングが正しくないと、クエリ実行時にマッピングエラーが発生します。

postgres=> select * from mysqltbl;
ERROR: failed to prepare the MySQL query:
Unknown column 'a' in 'field list'

さいごに

本日はmysql_fdwを使ってPostgreSQLからMySQLへ接続してみました。
PostgreSQLのForeign Data Wrapper(外部データラッパー?)を知らなかったのですが、oracle_fdwやpostgres_fdwもあったのですね。
また、今回のマイナーバージョンアップからtds_fdwもサポートに追加され、Microsoft SQL Serverへの外部接続も出来るようになったみたいです。